/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package databaseLayer;

import java.sql.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import javax.naming.NamingException;


/**
 *
 * @author Rigsby Chi
 */
public class ManagerUpdate {
    public static void main(String arg[]){
        //System.out.println(ManagerUpdate.getNumberOfHouse());
        //for(int i=0; i<10; i++)
        //ManagerUpdate.addMovieSlot("Movie1", "2014-01-01 00:00:01", 40, 3);
        //ManagerUpdate.removeMovieSlot("Movie1+4");
        //ManagerUpdate.changeMovieSlotStartTime("Movie1+1", "2013-04-18 12:00:00");
        ManagerUpdate.enableOrDisableSeat("Movie3+1", 1);
    }
    
    public static int getNumberOfHouse(){
        int numberOfHouse = 0;
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();
            
            PreparedStatement pstmt = con.prepareStatement("SELECT count(*) FROM house");
            
            ResultSet rs = pstmt.executeQuery();
            
            if(rs != null && rs.next())
                numberOfHouse = rs.getInt(1);
            
            if (rs !=null){
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close(); //close Statement object
            }
            if (con != null) {
                con.close(); //close Connection object 
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException e) {
            e.printStackTrace();
        }
        return numberOfHouse;
    }
    
    public static void addMovieSlot(String movieName, String startTime, int price, int hid){
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();
            
            int newslotID = 0;
            
            PreparedStatement pstmt = con.prepareStatement("SELECT count(*) FROM Movieslot where MOVIENAME=?");
            pstmt.setString(1, movieName);
            
            ResultSet rs = pstmt.executeQuery();
            
            if(rs != null && rs.next())
                newslotID = rs.getInt(1);
            
            newslotID++;
            
            pstmt = con.prepareStatement("INSERT INTO Movieslot VALUES (?, ?, ?, ?, ?, 1)");
            pstmt.setString(1, movieName + "-" + newslotID);
            pstmt.setString(2, startTime);
            pstmt.setInt(3, price);
            pstmt.setString(4, movieName);
            pstmt.setInt(5, hid);
            
            pstmt.executeUpdate();
            
            if (pstmt != null) {
                pstmt.close(); //close Statement object
            }
            if (con != null) {
                con.close(); //close Connection object 
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }
        
    public static void removeMovieSlot(String slotID){
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();
            
            int newslotID = 0;
            
            PreparedStatement pstmt = con.prepareStatement("UPDATE movieslot SET SLOTSTATE=0 WHERE slotid=?");
            pstmt.setString(1, slotID);
            
            pstmt.executeUpdate();
            
            if (pstmt != null) {
                pstmt.close(); //close Statement object
            }
            if (con != null) {
                con.close(); //close Connection object 
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }
    
    public static void changeMovieSlotStartTime(String slotID, String newStartTime){
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();
            
            int newslotID = 0;
            
            PreparedStatement pstmt = con.prepareStatement("UPDATE movieslot SET starttime=? WHERE slotid=?");
            pstmt.setString(1, newStartTime);
            pstmt.setString(2, slotID);
            
            pstmt.executeUpdate();
            
            if (pstmt != null) {
                pstmt.close(); //close Statement object
            }
            if (con != null) {
                con.close(); //close Connection object 
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }
    
    public static void changeMovieSlotPrice(String slotID, int newPrice){
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();
            
            int newslotID = 0;
            
            PreparedStatement pstmt = con.prepareStatement("UPDATE movieslot SET price=? WHERE slotid=?");
            pstmt.setInt(1, newPrice);
            pstmt.setString(2, slotID);
            
            pstmt.executeUpdate();
            
            if (pstmt != null) {
                pstmt.close(); //close Statement object
            }
            if (con != null) {
                con.close(); //close Connection object 
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }
    
    public static void enableOrDisableSeat(String slotID, int seatNo){
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();
            
            PreparedStatement pstmt = con.prepareStatement("select count(*) from SeatDisable where SLOTID=? and SEAT=?");
            pstmt.setString(1, slotID);
            pstmt.setInt(2, seatNo);
            ResultSet rs = pstmt.executeQuery();
            
            if(rs!= null && rs.next()){
                if(rs.getInt(1)==0){
                    System.out.println("Insert");
                    pstmt = con.prepareStatement("insert into SeatDisable values (?, ?)");
                    pstmt.setString(1, slotID);
                    pstmt.setInt(2, seatNo);
                    pstmt.executeUpdate();
                } else if (rs.getInt(1)==1){
                    pstmt = con.prepareStatement("delete from SeatDisable where SLOTID=? and SEAT=?");
                    System.out.println("delete");
                    pstmt.setString(1, slotID);
                    pstmt.setInt(2, seatNo);
                    pstmt.executeUpdate();
                }
            }
            
            if(rs != null){
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close(); //close Statement object
            }
            if (con != null) {
                con.close(); //close Connection object 
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }
}
